﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Web.UI.WebControls;

namespace QuanLyVanBanBUS
{
    public class VanBanBUS
    {
        public Boolean kiemtraTonTaiMaVanBan()
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = storeDB.VanBans.Select(q => q.MaVanBan).ToList();

            if (query.Count > 0)
            {
                return true;
            }

            return false;
        }

        public Boolean kiemtraNamHienTai()
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32(storeDB.VanBans.Select(m => m.MaVanBan).Max().Substring(0, 4));

            if (namHienTai >= namMax)
            {
                return true;
            }

            return false;
        }

        public string layMaVanBanMax()
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            if (kiemtraTonTaiMaVanBan() == true)
            {
                if (kiemtraNamHienTai() == true)
                {
                    string maVanBanMax = storeDB.VanBans.Select(m => m.MaVanBan).Max().Substring(4, 6);

                    return maVanBanMax;
                }
            }

            return null;
        }

        public string layMaVanBanMaxVuaThemVao(string soVanBan)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            string maVanBanMax = storeDB.VanBans.Where(s => s.SoVanBan == soVanBan).Select(m => m.MaVanBan).Max().ToString();

            return maVanBanMax;
        }

        public void themVanBan(VanBan vbDTO)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            storeDB.VanBans.InsertOnSubmit(vbDTO);

            storeDB.SubmitChanges();
        }

        public void suaVanBan(VanBan vbDTO, string soVanBan)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          where vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var _query = storeDB.VanBans.Where(q => q.SoVanBan == soVanBan && namHienTai == namMax).ToList();
            
            var query = _query.Last();

            query.SoVanBan = string.Empty;
            query.SoVanBan = vbDTO.SoVanBan;
            query.TenVanBan = vbDTO.TenVanBan;
            query.TenFile = vbDTO.TenFile;
            query.MaLoaiVanBan = vbDTO.MaLoaiVanBan;
            query.MaHinhThuc = vbDTO.MaHinhThuc;
            query.MaTinhChat = vbDTO.MaTinhChat;
            query.MaPhamVi = vbDTO.MaPhamVi;
            query.MaNoiPhatHanh = vbDTO.MaNoiPhatHanh;
            query.TenDonViSoanThao = vbDTO.TenDonViSoanThao;
            query.NgayHieuLuc = vbDTO.NgayHieuLuc;
            query.NgayHetHieuLuc = vbDTO.NgayHetHieuLuc;
            query.NoiDungVanBan = vbDTO.NoiDungVanBan;
            query.NoiDungButPhe = vbDTO.NoiDungButPhe;
            query.NgaySoanThao = vbDTO.NgaySoanThao;

            storeDB.SubmitChanges();
        }

        public Boolean kiemtraTonTaiSoVanBan(string soVanBan, string maNoiPhatHanh)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var temp = storeDB.VanBans.ToList();
            if (temp.Count > 0)
            {
                var temp2 = storeDB.VanBans.Where(s => s.SoVanBan == soVanBan && s.MaNoiPhatHanh == maNoiPhatHanh).ToList();
                if (temp2.Count > 0)
                {
                    int namMax = Convert.ToInt32(storeDB.VanBans.Where(s => s.SoVanBan == soVanBan && s.MaNoiPhatHanh == maNoiPhatHanh).Select(m => m.MaVanBan).Max().Substring(0, 4));

                    int namHienTai = DateTime.Now.Date.Year;

                    //string _soVanBan = soVanBan.Substring(0, 4);

                    var query = storeDB.VanBans.Where(q => q.SoVanBan == soVanBan && namMax == namHienTai && q.MaNoiPhatHanh == maNoiPhatHanh).ToList();
                    if (query.Count > 0)
                    {
                        return true;
                    }

                    return false;
                }
            }

            return false;
        }

        public void duyetSoanThao(GridView gridView, string maNguoiKiemSoat, string maPhamVi)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            for (int i = 0; i < gridView.Rows.Count; i++)
            {
                Label label = (Label)(gridView.Rows[i].Cells[1].FindControl("lblNoiNhan"));

                TextBox txtNoiDungButPhe = (TextBox)(gridView.Rows[i].Cells[1].FindControl("txtNoiDungButPhe"));

                CheckBox _checkBox = (CheckBox)(gridView.Rows[i].Cells[0].FindControl("chkMaVanBan"));

                if (_checkBox.Checked == true)
                {
                    string maVanBanCanDuyet = gridView.DataKeys[i].Value.ToString();
                    if (label.Text != string.Empty)
                    {
                        //Table VanBan
                        var query = storeDB.VanBans.Where(q => q.MaVanBan == maVanBanCanDuyet && q.MaNguoiKiemSoat == null && q.NgayKiemSoat == null && q.NgayXoaHienThi == null && q.GhiChu == null).First();
                        query.MaNguoiKiemSoat = maNguoiKiemSoat;
                        //query.NgayKiemSoat = Convert.ToDateTime(DateTime.Now.ToString().Substring(0,10));
                        query.NgayKiemSoat = Convert.ToDateTime(DateTime.Now);
                        query.MaPhamVi = maPhamVi;
                        query.MaTrangThai = "DD";
                        query.NoiHienThi = 1;
                        query.NoiDungButPhe = txtNoiDungButPhe.Text;

                        //Table NoiNhan
                        NoiNhanBUS nnBUS = new NoiNhanBUS();
                        bool kiemtra = nnBUS.kiemtraTonTaiMaVanBanTableNoiNhan(maVanBanCanDuyet);
                        if (kiemtra == true)
                        {
                            var query2 = storeDB.NoiNhans.Where(q => q.MaVanBan == maVanBanCanDuyet).ToList();
                            foreach (NoiNhan lstNoiNhan in query2)
                            {
                                lstNoiNhan.KiemSoat = 1;
                            }
                        }
                    }
                    else
                    {
                        //Table VanBan
                        var query = storeDB.VanBans.Where(q => q.MaVanBan == maVanBanCanDuyet && q.MaNguoiKiemSoat == null && q.NgayKiemSoat == null && q.NgayXoaHienThi == null && q.GhiChu == null).First();
                        query.MaNguoiKiemSoat = maNguoiKiemSoat;
                        query.NgayKiemSoat = DateTime.Now;
                        query.MaPhamVi = maPhamVi;
                        query.MaTrangThai = "DD";
                        query.NoiHienThi = 0;
                        query.NoiDungButPhe = txtNoiDungButPhe.Text;
                    }

                    storeDB.SubmitChanges();
                }
            }
        }

        public IList<VanBan> layDanhSachVanBanChuaDuyetTheoMaPhong(string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong
                         select vb).OrderByDescending(q => q.NgaySoanThao).ToList();
            
            return query;
        }

        public IList<VanBan> layDanhSachVanBanDaDuyetTheoMaPhong(string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat != null && vb.NgayKiemSoat != null && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.MaTrangThai == "DD" && nd.MaPhong == maPhong
                         select vb).ToList();

            return query;
        }

        public IList<VanBan> layDanhSachVanBanDaDuyetCapChiNhanh()
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = storeDB.VanBans.Where(q => q.MaPhamVi == "CN" && q.NgayKiemSoat != null && q.NgayXoaHienThi == null && q.GhiChu == null && q.NoiHienThi == 0).OrderByDescending(q => q.NgayKiemSoat).ToList();

            return query; 
        }

        public IList<VanBan> layDanhSachVanBanDaDuyetCapPhongTo()
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = storeDB.VanBans.Where(q => q.MaPhamVi == "PT" && q.NgayKiemSoat != null && q.NgayXoaHienThi == null && q.GhiChu == null && q.NoiHienThi == 0).OrderByDescending(q => q.NgayKiemSoat).ToList();

            return query;
        }

        public IList<VanBan> layChiTietVanBanTheoMaVanBan(string maVanBan)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = storeDB.VanBans.Where(q => q.MaVanBan == maVanBan).ToList();

            return query;
        }

        public IList<VanBan> timkiemSoVanBan(string soVanBan)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = storeDB.VanBans.Where(q => q.SoVanBan == soVanBan).ToList();

            if (query.Count > 0)
            {
                return query;
            }

            return null;
        }

        public Boolean kiemtraTonTaiSoVanBanCanTim(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb).ToList();

            if (query.Count > 0)
            {
                return true;
            }

            return false;
        }

        public string layTenVanBanCanTim(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" &&  nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax 
                         select vb.TenVanBan).ToList();

            return query.Last();
        }

        public string layTenFileCanTim(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb.TenFile).ToList();

            return query.Last();
        }

        public string layMaLoaiVanBan(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb.MaLoaiVanBan).ToList();

            return query.Last();
        }

        public string layMaHinhThuc(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb.MaHinhThuc).ToList();

            return query.Last();
        }

        public string layMaNoiPhatHanh(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb.MaNoiPhatHanh).ToList();

            return query.Last();
        }

        public string layTenDonViSoanThao(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb.TenDonViSoanThao).ToList();

            return query.Last();
        }

        public string layMaTinhChat(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb.MaTinhChat).ToList();

            return query.Last();
        }
        
        public string layNgayHieuLuc(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb.NgayHieuLuc).ToList();

            return query.Last().ToString();
        }

        public string layNgayHetHieuLuc(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb.NgayHetHieuLuc).ToList();

            return query.Last().ToString();
        }

        public string layNoiDungVanBan(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb.NoiDungVanBan).ToList();

            return query.Last();
        }

        public string layNoiDungButPhe(string soVanBan, string maPhong)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && nd.MaPhong == maPhong && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb.NoiDungButPhe).ToList();

            return query.Last();
        }

        public string layMaVanBanTheoSoVanBan(string soVanBan)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            int namHienTai = DateTime.Now.Date.Year;

            int namMax = Convert.ToInt32((from vb in storeDB.VanBans
                                          join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                                          where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && vb.SoVanBan == soVanBan
                                          select vb.MaVanBan).Max().Substring(0, 4));

            var query = (from vb in storeDB.VanBans
                         join nd in storeDB.NguoiDungs on vb.MaNguoiSoanThao equals nd.MaNguoiDung
                         where vb.MaNguoiKiemSoat == null && vb.NgayKiemSoat == null && vb.NgayXoaHienThi == null && vb.MaTrangThai == "CD" && vb.SoVanBan == soVanBan && namHienTai == namMax
                         select vb.MaVanBan).ToList();

            return query.Last();
        }

        public IList<VanBan> timkiemVanBan(VanBan vbDTO, DateTime ngayTu, DateTime ngayDen)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            if (vbDTO.SoVanBan != string.Empty && ngayTu.ToString() == string.Empty && ngayDen.ToString() == string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             where vb.SoVanBan.Contains(vbDTO.SoVanBan) && vb.NgayKiemSoat != null && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.NoiHienThi == 0
                             select vb).ToList();

                return query;
            }
            
            if (vbDTO.SoVanBan != string.Empty && ngayTu.ToString() != string.Empty && ngayDen.ToString() != string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             where vb.SoVanBan.Contains(vbDTO.SoVanBan) && vb.NgayKiemSoat != null && vb.NgayKiemSoat.Value.Date >= ngayTu && vb.NgayKiemSoat.Value.Date <= ngayDen && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.NoiHienThi == 0
                             select vb).ToList();

                return query;
            }

            if (vbDTO.TenVanBan != string.Empty && ngayTu.ToString() == string.Empty && ngayDen.ToString() == string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             where vb.TenVanBan.Contains(vbDTO.TenVanBan) && vb.NgayKiemSoat != null && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.NoiHienThi == 0
                             select vb).ToList();

                return query;
            }

            if (vbDTO.TenVanBan != string.Empty && ngayTu.ToString() != string.Empty && ngayDen.ToString() != string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             where vb.TenVanBan.Contains(vbDTO.TenVanBan) && vb.NgayKiemSoat.Value.Date >= ngayTu && vb.NgayKiemSoat.Value.Date <= ngayDen && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.NoiHienThi == 0
                             select vb).ToList();

                return query;
            }

            if (vbDTO.SoVanBan == string.Empty && vbDTO.TenVanBan == string.Empty && ngayTu.Date.ToString() != string.Empty && ngayDen.Date.ToString() != string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             where vb.NgayKiemSoat.Value.Date >= ngayTu && vb.NgayKiemSoat.Value.Date <= ngayDen && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.NoiHienThi == 0
                             select vb).ToList();

                return query;
            }

            return null;
        }

        public void xoaHienThi(VanBan vbDTO)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = storeDB.VanBans.Where(q => q.MaVanBan == vbDTO.MaVanBan).First();

            query.NgayXoaHienThi = vbDTO.NgayXoaHienThi;
            query.GhiChu = vbDTO.GhiChu;

            storeDB.SubmitChanges();
        }

        public IList<VanBan> timkiemDanhSachVanBanTheoMaNguoiDung(string maNguoiDung)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            /*if (vbDTO.SoVanBan != string.Empty && ngayTu.ToString() == string.Empty && ngayDen.ToString() == string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             join nn in storeDB.NoiNhans on vb.MaVanBan equals nn.MaVanBan
                             where nn.MaNguoiNhan == maNguoiDung && nn.KiemSoat == 1 && nn.BiXoa == 0 && vb.SoVanBan.Contains(vbDTO.SoVanBan) && vb.NgayKiemSoat != null && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.NoiHienThi == 0
                             select vb).ToList();

                return query;
            }

            if (vbDTO.SoVanBan != string.Empty && ngayTu.ToString() != string.Empty && ngayDen.ToString() != string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             join nn in storeDB.NoiNhans on vb.MaVanBan equals nn.MaVanBan
                             where nn.MaNguoiNhan == maNguoiDung && nn.KiemSoat == 1 && nn.BiXoa == 0 && vb.SoVanBan.Contains(vbDTO.SoVanBan) && vb.NgayKiemSoat != null && vb.NgayKiemSoat.Value.Date >= ngayTu && vb.NgayKiemSoat.Value.Date <= ngayDen && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.NoiHienThi == 0
                             select vb).ToList();

                return query;
            }

            if (vbDTO.TenVanBan != string.Empty && ngayTu.ToString() == string.Empty && ngayDen.ToString() == string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             join nn in storeDB.NoiNhans on vb.MaVanBan equals nn.MaVanBan
                             where nn.MaNguoiNhan == maNguoiDung && nn.KiemSoat == 1 && nn.BiXoa == 0 && vb.TenVanBan.Contains(vbDTO.TenVanBan) && vb.NgayKiemSoat != null && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.NoiHienThi == 0
                             select vb).ToList();

                return query;
            }

            if (vbDTO.TenVanBan != string.Empty && ngayTu.ToString() != string.Empty && ngayDen.ToString() != string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             join nn in storeDB.NoiNhans on vb.MaVanBan equals nn.MaVanBan
                             where nn.MaNguoiNhan == maNguoiDung && nn.KiemSoat == 1 && nn.BiXoa == 0 && vb.TenVanBan.Contains(vbDTO.TenVanBan) && vb.NgayKiemSoat.Value.Date >= ngayTu && vb.NgayKiemSoat.Value.Date <= ngayDen && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.NoiHienThi == 0
                             select vb).ToList();

                return query;
            }

            if (vbDTO.SoVanBan == string.Empty && vbDTO.TenVanBan == string.Empty && ngayTu.Date.ToString() != string.Empty && ngayDen.Date.ToString() != string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             join nn in storeDB.NoiNhans on vb.MaVanBan equals nn.MaVanBan
                             where nn.MaNguoiNhan == maNguoiDung && nn.KiemSoat == 1 && nn.BiXoa == 0 && vb.NgayKiemSoat.Value.Date >= ngayTu && vb.NgayKiemSoat.Value.Date <= ngayDen && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.NoiHienThi == 0
                             select vb).ToList();

                return query;
            }
            
             * return null;*/

            var query = (from vb in storeDB.VanBans
                         join nn in storeDB.NoiNhans on vb.MaVanBan equals nn.MaVanBan
                         where nn.MaNguoiNhan == maNguoiDung && nn.KiemSoat == 1 && nn.BiXoa == 0 && vb.NgayKiemSoat != null && vb.NgayXoaHienThi == null && vb.GhiChu == null && vb.NoiHienThi == 1
                         select vb).OrderByDescending(q => q.NgayKiemSoat).ToList();

            return query;
        }

        public IList<VanBan> timkiemVanBanTheoNgay(DateTime ngayTu, DateTime ngayDen)
        {
            //for (int i = 0; i < gridView.Rows.Count; i++ )
            //{
            //    CheckBox _checkBox = (CheckBox)(gridView.Rows[i].Cells[0].FindControl("chkMaNguoiDung"));

            //    if (_checkBox.Checked == false)
            //    {
            //        QLVBDataContext storeDB = new QLVBDataContext();

            //        var query = storeDB.VanBans.Where(q => q.NgayKiemSoat >= ngayTu && q.NgayKiemSoat <= ngayDen).ToList();

            //        return query;
            //    }
            //}

            //return null;

            QLVBDataContext storeDB = new QLVBDataContext();

            var query = storeDB.VanBans.Where(q => q.NgayKiemSoat.Value.Date >= ngayTu.Date && q.NgayKiemSoat.Value.Date <= ngayDen.Date).ToList();

            return query;
        }

        public IList<VanBan> layDanhSachVanBanDaDuyetTheoMaNguoiDung(VanBan vbDTO, DateTime ngayTu, DateTime ngayDen)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            if (ngayTu.ToString() == string.Empty && ngayDen.ToString() == string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             where (vb.MaNguoiSoanThao == vbDTO.MaNguoiSoanThao || vb.MaNguoiKiemSoat == vbDTO.MaNguoiKiemSoat) && vb.NgayKiemSoat != null && vb.MaTrangThai == "DD"
                             select vb).OrderByDescending(q => q.NgayKiemSoat).ToList();

                return query;
            }

            if (ngayTu.ToString() != string.Empty && ngayDen.ToString() != string.Empty)
            {
                var query = (from vb in storeDB.VanBans
                             where (vb.MaNguoiSoanThao == vbDTO.MaNguoiSoanThao || vb.MaNguoiKiemSoat == vbDTO.MaNguoiKiemSoat) && vb.NgayKiemSoat != null && vb.NgayKiemSoat.Value.Date >= ngayTu && vb.NgayKiemSoat.Value.Date <= ngayDen && vb.MaTrangThai == "DD"
                             select vb).ToList();

                return query;
            }

            return null;
        }

        public IList<VanBan> rptDanhSachVanBanDen(DateTime ngayTu, DateTime ngayDen, string maHinhThuc)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = storeDB.VanBans.Where(q => q.NgayKiemSoat.Value.Date >= ngayTu.Date && q.NgayKiemSoat.Value.Date <= ngayDen.Date && q.MaHinhThuc == maHinhThuc && q.MaTrangThai == "DD" && q.MaHinhThuc == "DE").ToList();

            return query;
        }

        public IList<VanBan> rptDanhSachVanBanDi(DateTime ngayTu, DateTime ngayDen, string maHinhThuc)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = storeDB.VanBans.Where(q => q.NgayKiemSoat.Value.Date >= ngayTu.Date && q.NgayKiemSoat.Value.Date <= ngayDen.Date && q.MaHinhThuc == maHinhThuc && q.MaTrangThai == "DD" && q.MaHinhThuc == "DI").ToList();

            return query;
        }

        public IList<VanBan> timkiemSoVanBanDaNhanTheoMaNguoiDung(string soVanBan, string maNguoiDung)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = (from vb in storeDB.VanBans
                         join nn in storeDB.NoiNhans on vb.MaVanBan equals nn.MaVanBan
                         where vb.SoVanBan == soVanBan && vb.NoiHienThi == 1 && nn.MaNguoiNhan == maNguoiDung && nn.KiemSoat == 1 && nn.BiXoa == 0
                         select vb).ToList();

            return query;
        }

        public IList<VanBan> layDanhSachVanBanDaNhanTrongNgayTheoMaNguoiNhan(NoiNhan nnDTO)
        {
            QLVBDataContext storeDB = new QLVBDataContext();

            var query = (from vb in storeDB.VanBans
                         join nn in storeDB.NoiNhans on vb.MaVanBan equals nn.MaVanBan
                         where nn.MaNguoiNhan == nnDTO.MaNguoiNhan && nn.NgayNhan.Value.Date == DateTime.Today.Date && nn.KiemSoat == 1 && nn.BiXoa == 0
                         select vb).ToList();

            return query;
        }
    }
}